import type { Router } from "express";
import type { Ioracle, IcallbackFn } from "../../types";
import { decrypt } from "../../utils/privateKey";
import { sendMail } from "./mail";
import multer from "multer";
import fs from "fs";
import { BindParameters } from "oracledb";

export async function SendMailLoop(params: any) {
  for (let i = 0; i < params.data.length; i++) {
    const chinese_name = params.data[i].chinese_name;
    const sign_flag = params.data[i].sign_flag;
    await sendMail({
      mailTo: params.data[i].email as string,
      mailBcc: "xiaowei1@compeq.huizhou.gd.cn;yufangwei@compeq.huizhou.gd.cn;",
      mailSubject: "績效輔導改善系統--通知郵件",
      mailHtml:
        sign_flag === "N"
          ? "./mail/improve_achieve/send_approve.ejs"
          : "./mail/improve_achieve/sign.ejs",
      mailData: {
        chinese_name,
      },
    });
  }
}

const storage = multer.diskStorage({
  //上传文件到服务器的存储位置
  destination: "../uploadFile/hr_improve_achieve/sign",
  filename: function (_req, _file, callback) {
    // 获取时间戳
    var filename = new Date().getTime();
    // 文件的命名为：时间戳 + 文件附件名 + 文件的后缀名
    callback(null, filename + "-tempname" + ".png");
  },
});
const upload = multer({
  storage: storage,
});

export default (router: Router, oracle: Ioracle, callbackFn: IcallbackFn) => {
  //獲取當前任務
  router.post("/getuinfo", (req, res) => {
    const { docid, username, type } = req.body;
    let sql = "";
    if (type === "end") {
      sql =
        " select a.no badge,a.name_in_chinese chinese_name,b.description position,'Y' manager_result,'end' flow_code,'Y' manager_save_flag,'Y' user_accept_flag,'結案' flow_phase";
      sql +=
        " from personnel_basic a, job_position b,xxhr_improve_achieve_header c,xxhr_improve_achieve_interview d";
      sql += ` where a.pos_code = b.code and a.no = c.badge and c.docid ='${docid}'`;
      sql +=
        " and c.docid = d.docid";
    } else {
      sql =
        " select a.no badge,a.name_in_chinese chinese_name,b.description position,d.manager_result,e.flow_code,d.manager_save_flag,d.user_accept_flag,e.flow_phase";
      sql +=
        " from personnel_basic a, job_position b,xxhr_improve_achieve_header c,xxhr_improve_achieve_interview d,xxhr_improve_achieve_flow e";
      sql += ` where a.pos_code = b.code and a.no = c.badge and c.docid ='${docid}'`;
      sql +=
        " and c.docid = d.docid and c.status = 'N' and c.docid = e.docid and c.current_flow = e.flow";
      sql += ` and e.klm = '${decrypt(username)}'`;
    }
    callbackFn(res, oracle.connHrm, { sql });
  });

  router.post("/interviewinfo", (req, res) => {
    const { docid } = req.body;
    let sql =
      " select a.docid,b.assessment_type,b.assessment_result,b.assessment_date,to_char(b.interview_date,'yyyy-mm-dd')interview_date,";
    sql +=
      " b.assessment_next,b.other_comments";
    sql +=
      " from xxhr_improve_achieve_header a,xxhr_improve_achieve_interview b";
    sql += ` where a.docid = b.docid and a.docid = '${docid}'`;
    callbackFn(res, oracle.connHrm, { sql });
  });

  router.post("/sendinterview", async (req, res) => {
    const { param } = req.body;
    const paramParse = JSON.parse(param);

    if(paramParse.lines.length === 0){
      res.send({ code: 0, message: "無數據傳入" });
      return
    }

    let sql = ` delete from xxhr_improve_interview_line where docid = '${paramParse.docid}'`;
    try {
      await oracle.connHrm({ sql });
    } catch {
      res.send({ code: 0, message: "保存數據錯誤" });
      return;
    }
    sql =
      " insert into xxhr_improve_interview_line values(:docid,:seq,:project,:target,:examine_describe,:improve_describe)";
    let bind: BindParameters[] = [];
    paramParse.lines.forEach((element: any) => {
      let obj = {
        docid: paramParse.docid,
        seq: element.seq,
        project: element.project,
        target: element.target,
        examine_describe: element.examine_describe,
        improve_describe: element.improve_describe,
      };
      bind.push(obj);
    });

    try {
      await oracle.connHrm({ sql, isMulti: true, params: bind });
    } catch {
      res.send({ code: 0, message: "保存數據錯誤" });
      return;
    }

    sql = " update xxhr_improve_achieve_interview  set ";
    sql += ` assessment_type = '${paramParse.assessment_type}'`;
    sql += ` ,assessment_result = '${paramParse.assessment_result}'`;
    sql += ` ,assessment_date = '${paramParse.assessment_date}'`;
    sql += ` ,interview_date = to_date('${paramParse.interview_date}','yyyy-mm-dd')`;
    sql += ` ,assessment_next = '${paramParse.assessment_next}'`;
    sql += ` ,last_update_time = sysdate,last_update_by='${decrypt(
      paramParse.last_update_by
    )}'`;
    sql += ` ,manager_save_flag = 'Y',other_comments = '${paramParse.other_comments}'`;
    sql += ` where docid = '${paramParse.docid}'`;
    let resSave, resSend, resMail;
    try {
      resSave = await oracle.connHrm({ sql });
    } catch {
      res.send({ code: 0, message: "明細已保存成功，但數據保存錯誤" });
      return;
    }
    if (resSave.code === 200 && paramParse.operation === "send") {
      try {
        sql = ` begin xxhr_improve_achieve_approve_p('${
          paramParse.docid
        }','${decrypt(paramParse.last_update_by)}','${paramParse.comments}','${
          paramParse.approve
        }',${paramParse.seq});end;`;
        resSend = await oracle.connHrm({ sql });
      } catch {
        res.send({ code: 0, message: "流程流向錯誤" });
        return;
      }
      if (resSend.code === 200) {
        try {
          sql =
            " select b.docid,b.flow_phase,substr(b.klm,9) || '@compeq.huizhou.gd.cn' email,c.chinese_name ";
          sql +=
            " ,case when b.flow_code like '%_sign_%' then 'Y' else 'N' end as sign_flag";
          sql +=
            " from xxhr_improve_achieve_header a,xxhr_improve_achieve_flow b,user_info_n c";
          sql += ` where a.docid = b.docid and a.current_flow = b.flow  and b.klm = c.account and a.docid = '${paramParse.docid}'`;
          resMail = await oracle.connHrm({ sql });
          await SendMailLoop(resMail);
          res.send({ code: 200, message: "success", data: [] });
        } catch {
          res.send({ code: 0, message: "已正常送出，郵件發送異常" });
        }
      }
    } else {
      res.send({ code: 200, message: "success", data: [] });
    }
  });

  router.post("/getlines", (req, res) => {
    const { docid, seq } = req.body;
    let sql =
      " select seq,project,target,examine_describe,improve_describe,rowid from xxhr_improve_interview_line";
    sql += ` where docid = '${docid}'`;
    sql += seq === 0 ? "" : ` and seq = ${seq}`;
    sql += " order by seq"
    callbackFn(res, oracle.connHrm, { sql });
  });

  router.post("/uploadsign", upload.single("file"), async (req, res) => {
    const { docid, flow_code, username, filename, seq } = req.body;
    if (filename && req.file?.filename) {
      //根據文件命名規則，文件被命名成了 時間戳 + -tempname + 後綴名的形式，這裏將tempname替換成為原始的文件
      //傳過來的文件名，只有文件名，不帶後綴
      //在改善表中可能會有多個簽名
      let newfileName =
        seq === "0" ? flow_code + ".png" : flow_code + "_" + seq + ".png";
      newfileName = docid + "_" + newfileName;
      fs.renameSync(
        "G:\\uploadFile\\hr_improve_achieve\\sign\\" +
          req.file?.filename.toString(),
        "G:\\uploadFile\\hr_improve_achieve\\sign\\" + newfileName
      );
      let sql = ` begin xxhr_improve_achieve_sign_p('${docid}','${flow_code}','${decrypt(
        username
      )}',${seq},'${newfileName}');end;`;
      try {
        await oracle.connHrm({ sql });
        sql =
          " select count(*)count from xxhr_improve_achieve_header a,xxhr_improve_achieve_flow b ";
        sql += ` where a.docid = b.docid and a.current_flow = b.flow and a.docid ='${docid}'`;
        const resC = await oracle.connHrm({ sql });
        if (resC.code === 200 && resC.data[0].count === 1) {
          sql =
            " select b.docid,b.flow_phase,substr(b.klm,9) || '@compeq.huizhou.gd.cn' email,c.chinese_name ";
          sql +=
            " ,case when b.flow_code like '%_sign_%' then 'Y' else 'N' end as sign_flag";
          sql +=
            " from xxhr_improve_achieve_header a,xxhr_improve_achieve_flow b,user_info_n c";
          sql += ` where a.docid = b.docid and a.current_flow = b.flow and b.klm = c.account and a.docid = '${docid}'`;
          const resMail = await oracle.connHrm({ sql });
          await SendMailLoop(resMail);
          res.send({ code: 200, message: "success", data: [] });
        } else {
          res.send({ code: 200, message: "success", data: [] });
        }
      } catch (err) {
        res.send({ code: 0, message: err });
      }
    } else {
      res.send({ code: 200, message: "success", data: [] });
    }
  });

  return router;
};
